package com.hy.dao.impl;
/*
 * @ Date: 2024/5/14 22:35
 * @ Description: ClienttableDaoImpl
 **/

import com.hy.bean.Clienttable;
import com.hy.dao.ClienttableDao;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.util.List;
import java.sql.Connection;
import java.sql.SQLException;

public class ClienttableDaoImpl implements ClienttableDao {

    private static QueryRunner qr = new QueryRunner();
    private static BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());

    @Override
    public Clienttable queryById(Integer id) {
        Clienttable clienttable = null;

        try {
            Connection conn = DBManager.getConnection();

            String sql = "select * from clienttable where id = ?";

            clienttable = qr.query(conn,sql,new BeanHandler<>(Clienttable.class,basicRowProcessor),id);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }
        return clienttable;
    }

    @Override
    public List<Clienttable> queryAll(Integer page,Integer limit) {
        List<Clienttable> orderstables = null;

        try {
            Connection conn = DBManager.getConnection();

            String sql = "select * from clienttable limit ?,?";

            orderstables = qr.query(conn,sql,new BeanListHandler<>(Clienttable.class,basicRowProcessor),(page-1)*limit,limit);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }
        return orderstables;
    }

    @Override
    public List<Clienttable> queryAllByLimit() {
        return null;
    }

    @Override
    public Long count() {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select count(1) from clienttable";
            Long query = qr.query(connection, sql, new ScalarHandler<>());

            return query;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }

    @Override
    public Clienttable queryByClientName(String clientName) {
        Connection conn = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from clienttable where clientName = ?";
            return qr.query(conn, sql, new BeanHandler<>(Clienttable.class), clientName);
        } catch (Exception e) {
            throw new BizException("数据库连接异常",e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public int insert(Clienttable clienttable) {
        Connection conn = null;
        int count = 0;
        try {
            conn = DBManager.getConnection();

            String sql = "insert into clienttable(id,clientNumber,clientName,clientAddress,clientManager" +
                    ",postalcode,phoneNumber,linkman,shortcut,province,email,taxNumber,bandNumber" +
                    ",openBank,type,remark) values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            return qr.update(conn, sql,clienttable.getClientnumber(),clienttable.getClientname()
                    ,clienttable.getClientaddress(),clienttable.getClientmanager(),clienttable.getPostalcode()
                    ,clienttable.getPhonenumber(),clienttable.getLinkman(),clienttable.getShortcut()
                    ,clienttable.getProvince(),clienttable.getEmail(),clienttable.getTaxnumber()
                    ,clienttable.getBandnumber(),clienttable.getOpenbank()
                    ,clienttable.getType(),clienttable.getRemark());
        } catch (Exception e){
            throw new BizException("数据库连接异常",e);
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public int update(Clienttable clienttable) {
        int count = 0;

        try {
            Connection conn = DBManager.getConnection();

            String sql = "UPDATE `clienttable` SET " +
                    "`clientNumber` = ?, `clientName` = ?, `clientAddress` = ?," +
                    " `clientManager` = ?, `postalcode` = ?, `phoneNumber` = ?," +
                    " `linkman` = ?, `shortcut` = ?, `province` = ?, " +
                    "`email` = ?, `taxNumber` = ?, `bandNumber` = ?," +
                    " `openBank` = ?, `type` = ?, `remark` = ?" +
                    " WHERE `id` = ?";

            count = qr.update(conn,sql,clienttable.getClientnumber(),clienttable.getClientname(),clienttable.getClientaddress(),clienttable.getClientmanager(),clienttable.getPostalcode(),clienttable.getPhonenumber(),clienttable.getLinkman(),clienttable.getShortcut(),clienttable.getProvince(),clienttable.getEmail(),clienttable.getTaxnumber(),clienttable.getBandnumber(),clienttable.getOpenbank(),clienttable.getType(),clienttable.getRemark(),clienttable.getId());
            DbUtils.close(conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public int deleteById(Integer id) {
        int count = 0;

        try {
            Connection conn = DBManager.getConnection();

            String sql = "delete from clienttable where id = ?";

            count = qr.update(conn,sql,id);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public List<Clienttable> selectPrintClientLimit(Integer pages, Integer currentCount, String clientname, String linkman, String type) {
        List<Clienttable> clienttableList = null;

        try {
            Connection conn = DBManager.getConnection();

            String sql = "select * from clienttable where clientName like ? and linkman like ? and type like ? limit ?,?";

            clienttableList = qr.query(conn,sql,new BeanListHandler<>(Clienttable.class,basicRowProcessor),"%"+clientname+"%","%"+linkman+"%","%"+type+"%",(currentCount-1)*pages,pages);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return clienttableList;
    }


    @Override
    public Long selectCount( String clientname, String linkman, String type) {
        long count = 0;
        try {
            Connection conn = DBManager.getConnection();

            String sql = "select count(*) from clienttable where clientName like ? and linkman like ? and type like ?";

            count = qr.query(conn,sql,new ScalarHandler<>(),"%"+clientname+"%","%"+linkman+"%","%"+type+"%");

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return count;
    }

}
